package com.edu.dao.impl;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.edu.dao.BaseDao;
import com.edu.dao.UserDao;
import com.edu.entity.GreenUser;
import com.edu.entity.Reason;
import com.edu.entity.ReasonTa;
import com.edu.entity.Shi;
import com.edu.entity.ShiC;
import com.edu.entity.ShiCs;
import com.edu.entity.Tu;
import com.edu.entity.User;
import com.edu.entity.YuanC;

public class UserDaoImpl implements UserDao {

	@Override
	public List<User> allUser() {
		// TODO Auto-generated method stub
		List<User> userList = new ArrayList<User>();
		try {
			Connection conn = BaseDao.getConn();
			Statement ps = conn.createStatement();
			String sql = "SELECT * FROM user";
			System.out.println(sql);
			ResultSet rs = ps.executeQuery(sql);
			while (rs.next()) {
				User user = new User();
				user.setId(rs.getInt("id"));
				user.setUserName(rs.getString("userName"));
				user.setUserPass(rs.getString("userPass"));
				user.setEmail(rs.getString("userEmail"));
				user.setIdentity(rs.getString("userIdentity"));
				user.setCountry(rs.getString("country"));
				user.setSex(rs.getString("sex"));
				user.setOccupation(rs.getNString("userOccupation"));
				user.setAvatar(rs.getString("userAvatar"));
				user.setPhoto(rs.getNString("userPhoto"));
				userList.add(user);
			}
			BaseDao.closeAll(conn, ps, rs);
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		return userList;
	}

	@Override
	public boolean queryNameAndPass(String name, String pass) {
		// TODO Auto-generated method stub
		String sql = "SELECT * FROM user WHERE username ='"+name+"' AND userpass='"+pass+"' AND userIdentity ='管理员' ";
		boolean num =BaseDao.querys(sql);
		System.out.println(sql);
		return num;
	}

	@Override
	public int update(User user) {
		// TODO Auto-generated method stub
		String sql = "UPDATE user SET userName=?,userPass=?,userEmail=?,userOccupation=?,sex=?,userIdentity =? WHERE id=?";
		System.out.println(sql);
		Object[] obj= {user.getUserName(),user.getUserPass(),user.getEmail(),user.getOccupation(),user.getSex(),user.getIdentity(),user.getId()};
		int num=BaseDao.executeUpdate(sql,obj);
		return num;
	}

	@Override
	public int Delete(int id) {
		// TODO Auto-generated method stub
		String sql = "DELETE FROM user WHERE id="+id;
		System.out.println(sql);
		int num =BaseDao.executeUpdate(sql);
		return num;
	}

	public List<User> queryByIdAll(User user) {
		// TODO Auto-generated method stub
		List<User> userList = new ArrayList<User>();
		
		try {
			Connection conn = BaseDao.getConn();
			Statement ps = conn.createStatement();
			String sql = "SELECT * FROM user WHERE 1 = 1 ";
			//动态sql语句
			if (user.getId() != 0) {
			    sql += " and id like '%" + user.getId() + "%' ";
			}
			    if (user.getUserName() != null && !user.getUserName().isEmpty()) {
			        sql += " AND username LIKE '%" + user.getUserName() + "%' ";
			    }

			    if (user.getUserPass() != null && !user.getUserPass().isEmpty()) {
			        sql += " AND userPass LIKE '%" + user.getUserPass() + "%' ";
			    }
			    if (user.getEmail() != null && !user.getEmail().isEmpty()) {
			        sql += " AND userPass LIKE '%" + user.getEmail() + "%' ";
			    }
			    if (user.getSex() != null && !user.getSex().isEmpty()) {
			        sql += " AND userPass LIKE '%" + user.getSex() + "%' ";
			    }
			    if (user.getCountry() != null && !user.getCountry().isEmpty()) {
			        sql += " AND userPass LIKE '%" + user.getCountry() + "%' ";
			    }
			    if (user.getOccupation() != null && !user.getOccupation().isEmpty()) {
			        sql += " AND userPass LIKE '%" + user.getOccupation() + "%' ";
			    }
			System.out.println(sql);
			ResultSet rs = ps.executeQuery(sql);
			while (rs.next()) {
				User rsUser = new User();
				rsUser.setId(rs.getInt("id"));
				rsUser.setUserName(rs.getString("userName"));
				rsUser.setUserPass(rs.getString("userPass"));
				rsUser.setEmail(rs.getString("userEmail"));
				rsUser.setCountry(rs.getString("country"));
				rsUser.setIdentity(rs.getString("userIdentity"));
				rsUser.setSex(rs.getString("sex"));
				rsUser.setOccupation(rs.getString("userOccupation"));
				rsUser.setPhoto(rs.getString("userPhoto"));
				rsUser.setAvatar(rs.getString("userAvatar"));
				userList.add(rsUser);
			}
			BaseDao.closeAll(conn,ps,rs);
		} catch (Exception e) {
			// TODO: handle exception
		}
		return userList;
		
	}

	@Override
	public List<User> absoluteQuery(int id,User user) {
		// TODO Auto-generated method stub
		List<User> userList = new ArrayList<User>();
		try {
			Connection conn = BaseDao.getConn();
			Statement ps = conn.createStatement();
			String sql = "SELECT * FROM user where id="+id;
			System.out.println(sql);
			ResultSet rs = ps.executeQuery(sql);
			while (rs.next()) {
				User users = new User();
				users.setId(rs.getInt("id"));
				users.setUserName(rs.getString("userName"));
				users.setUserPass(rs.getString("userPass"));
				users.setEmail(rs.getString("userEmail"));
				users.setIdentity(rs.getString("userIdentity"));
				users.setCountry(rs.getString("country"));
				users.setSex(rs.getString("sex"));
				users.setOccupation(rs.getNString("userOccupation"));
				users.setAvatar(rs.getString("userAvatar"));
				users.setPhoto(rs.getNString("userPhoto"));
				userList.add(users);
			}
			BaseDao.closeAll(conn, ps, rs);
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		return userList;
	}

	@Override
	public int InsertUser(User user) {
		// TODO Auto-generated method stub
		String sql = "INSERT INTO user(userName,userPass,sex,userEmail,country,userOccupation,userIdentity,userAvatar,userPhoto) VALUES('"+user.getUserName()+"','"+user.getUserPass()+"','"+user.getSex()+"','"+user.getEmail()+"','"+user.getCountry()+"','"+user.getOccupation()+"','见习志愿者','images/bicycle.jpg','images/sunset-big.jpg')";
		int num =BaseDao.executeUpdate(sql);
		System.out.println(sql);
		return num;
	}

	@Override
	public List<User> queryByAUser() {
		// TODO Auto-generated method stub
		List<User> userList = new ArrayList<User>();
		try {
			Connection conn = BaseDao.getConn();
			Statement ps = conn.createStatement();
			String sql = "SELECT * FROM user where 1 = 1 AND userIdentity= '优秀志愿者'";
			System.out.println(sql);
			ResultSet rs = ps.executeQuery(sql);
			while (rs.next()) {
				User users = new User();
				users.setId(rs.getInt("id"));
				users.setUserName(rs.getString("userName"));
				users.setUserPass(rs.getString("userPass"));
				users.setEmail(rs.getString("userEmail"));
				users.setIdentity(rs.getString("userIdentity"));
				users.setCountry(rs.getString("country"));
				users.setSex(rs.getString("sex"));
				users.setOccupation(rs.getNString("userOccupation"));
				users.setAvatar(rs.getString("userAvatar"));
				users.setPhoto(rs.getNString("userPhoto"));
				userList.add(users);
			}
			BaseDao.closeAll(conn, ps, rs);
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		return userList;
	}

	@Override
	public List<YuanC> queryByAUser(YuanC yuanc) {
		// TODO Auto-generated method stub
			List<YuanC> userList = new ArrayList<YuanC>();
		
		try {
			Connection conn = BaseDao.getConn();
			Statement ps = conn.createStatement();
			String sql = "SELECT * FROM donations WHERE 1 = 1 ";
			//动态sql语句
			if (yuanc.getId() != 0) {
			    sql += " and id like '%" + yuanc.getId() + "%' ";
			}
			    if (yuanc.getAuthor() != null && !yuanc.getAuthor().isEmpty()) {
			        sql += " AND author LIKE '%" + yuanc.getAuthor() + "%' ";
			    }

			    if (yuanc.getTheme() != null && !yuanc.getTheme().isEmpty()) {
			        sql += " AND theme LIKE '%" + yuanc.getTheme() + "%' ";
			    }
			    if (yuanc.getTitle() != null && !yuanc.getTitle().isEmpty()) {
			        sql += " AND title LIKE '%" + yuanc.getTitle() + "%' ";
			    }
			    if (yuanc.getCollect() != 0) {
				    sql += " and collect like '%" + yuanc.getId() + "%' ";
				}
			    if (yuanc.getTargets() != 0) {
				    sql += " and targets like '%" + yuanc.getId() + "%' ";
				}
			System.out.println(sql);
			ResultSet rs = ps.executeQuery(sql);
			while (rs.next()) {
				YuanC rsUser = new YuanC();
				rsUser.setId(rs.getInt("id"));
				rsUser.setAuthor(rs.getString("author"));
				rsUser.setCollect(rs.getInt("collect"));
				rsUser.setTargets(rs.getInt("targets"));
				rsUser.setTheme(rs.getString("theme"));
				rsUser.setTitle(rs.getString("title"));
				userList.add(rsUser);
			}
			
			// 第二个查询语句
			if(yuanc.getId()==0) {
				String sql2 = "SELECT * FROM donationsContent WHERE 1 = 1";
				    System.out.println(sql2);
			        ps = conn.createStatement();
			        rs = ps.executeQuery(sql2);
			}else {
				 String sql2 = "SELECT * FROM donationsContent where id="+yuanc.getId();
				  System.out.println(sql2);
			        ps = conn.createStatement();
			        rs = ps.executeQuery(sql2);
			} 
	        int i = 0;
	        while (rs.next() && i < userList.size()) {
	        	YuanC rsUser = userList.get(i);
	            // 注意这里需要根据具体需求调整，假设 newNventContent 表的字段如下
	        	rsUser.setNei(rs.getString("donationsContent"));
				rsUser.setYuanNei(rs.getString("donationsContents"));
	            i++;
	        }
	        BaseDao.closeAll(conn, ps, rs);
		}catch (SQLException e) {
		        e.printStackTrace();
		    } 
		return userList;
		
	}

	@Override
	public List<ShiC> queryByAUser(ShiC shic) {
		// TODO Auto-generated method stub
     List<ShiC> userList = new ArrayList<ShiC>();
		try {
			Connection conn = BaseDao.getConn();
			Statement ps = conn.createStatement();
			String sql = "SELECT * FROM newNvent WHERE 1 = 1 ";
			//动态sql语句
			if (shic.getId() != 0) {
			    sql += " and id like '%" + shic.getId() + "%' ";
			}
			    if (shic.getAuthor() != null && !shic.getAuthor().isEmpty()) {
			        sql += " AND author LIKE '%" + shic.getAuthor() + "%' ";
			    }

			    if (shic.getDates() != null && !shic.getDates().isEmpty()) {
			        sql += " AND dates LIKE '%" + shic.getDates() + "%' ";
			    }
			    if (shic.getTitle() != null && !shic.getTitle().isEmpty()) {
			        sql += " AND title LIKE '%" + shic.getTitle() + "%' ";
			    }
			System.out.println(sql);
			ResultSet rs = ps.executeQuery(sql);
			while (rs.next()) {
				ShiC rsUser = new ShiC();
				rsUser.setId(rs.getInt("id"));
				rsUser.setAuthor(rs.getString("author"));
				rsUser.setDates(rs.getString("dates"));
				rsUser.setTitle(rs.getString("title"));
				userList.add(rsUser);
			}
			
			// 第二个查询语句
			if(shic.getId()==0) {
				String sql2 = "SELECT * FROM newNventContent WHERE 1 = 1";
				 System.out.println(sql2);
			        ps = conn.createStatement();
			        rs = ps.executeQuery(sql2);
			}else {
				  String sql2 = "SELECT * FROM newNventContent where id="+shic.getId();
				  System.out.println(sql2);
			        ps = conn.createStatement();
			        rs = ps.executeQuery(sql2);
			}
	        
	        int i = 0;
	        while (rs.next() && i < userList.size()) {
	        	ShiC rsUser = userList.get(i);
	            // 注意这里需要根据具体需求调整，假设 newNventContent 表的字段如下
	        	rsUser.setCout(rs.getString("newNventContent"));
				rsUser.setEventCategory(rs.getString("eventCategory"));
				rsUser.setPhone(rs.getString("phone"));
				rsUser.setPlace(rs.getString("place"));
	            i++;
	        }
	        BaseDao.closeAll(conn, ps, rs);
		}catch (SQLException e) {
		        e.printStackTrace();
		    } 
		return userList;
	}

	@Override
	public int Deletes(int id) {
		// TODO Auto-generated method stub
		  // 删除 donations 表中的记录
	    String sql1 = "DELETE FROM donations WHERE id=" + id;
	    System.out.println(sql1); // 调试输出删除语句
	    int num1 = BaseDao.executeUpdate(sql1); // 执行第一个删除操作

	    // 删除 donationsContent 表中的记录
	    String sql2 = "DELETE FROM donationsContent WHERE id=" + id;
	    System.out.println(sql2); // 调试输出删除语句
	    int num2 = BaseDao.executeUpdate(sql2); // 执行第二个删除操作

	    return num2; // 返回第二个删除操作影响的行数
	}

	@Override
	public int DeleteShi(int id) {
		// TODO Auto-generated method stub
		 // 删除 newNvent 表中的记录
	    String sql = "DELETE FROM newNvent WHERE id = ?";
	    System.out.println(sql); // 调试输出删除语句
	    Object[] obj= {id};
	    int num = BaseDao.executeUpdate(sql, obj); // 使用 PreparedStatement 执行安全的删除操作

	    // 删除 newNventContent 表中的记录
	    String sql2 = "DELETE FROM newNventContent WHERE id = ?";
	    System.out.println(sql2); // 调试输出删除语句
	    int num2 = BaseDao.executeUpdate(sql2, obj); // 使用 PreparedStatement 执行安全的删除操作

	    return num2; // 返回第二个删除操作影响的行数
	}

	@Override
	public int update(ShiC shic) {
		// TODO Auto-generated method stub
		String sql = "UPDATE newNvent SET title=?,author=?,dates=? WHERE id=?";
		System.out.println(sql);
		Object[] obj= {shic.getTitle(),shic.getAuthor(),shic.getDates(),shic.getId()};
		int num=BaseDao.executeUpdate(sql,obj);
		return num;
	}

	@Override
	public int InsertRe(Reason reason) {
		// TODO Auto-generated method stub
		String sql = "INSERT INTO donations(title,author,theme,targets,collect,img) VALUES('"+reason.getTitle()+"','"+reason.getAuthor()+"','"+reason.getTheme()+"',"+reason.getTargets()+","+reason.getCollect()+",'images/event_3.jpg')";
		int num =BaseDao.executeUpdate(sql);
		System.out.println(sql);
		return num;
	}

	@Override
	public int InsertRes(ReasonTa reasonTa) {
		// TODO Auto-generated method stub
		String sql = "INSERT INTO donationsContent(donationsContent,donationsContents,img) VALUES('"+reasonTa.getDonationsContent()+"','"+reasonTa.getDonationsContents()+"','images/event_3.jpg')";
		int num =BaseDao.executeUpdate(sql);
		System.out.println(sql);
		return num;
	}

	@Override
	public int InsertShi(Shi shi) {
		// TODO Auto-generated method stub
		String sql = "INSERT INTO newNvent(title,author,dates,img) VALUES('"+shi.getTitle()+"','"+shi.getAuthor()+"',NOW(),'images/event_2.jpg')";
		int num =BaseDao.executeUpdate(sql);
		System.out.println(sql);
		return num;
	}

	@Override
	public int InsertShics(ShiCs shiCs) {
		// TODO Auto-generated method stub
		String sql = "INSERT INTO newNventContent(newNventContent,organizer,place,cost,phone,eventCategory,email,website,img) VALUES('"+shiCs.getNewNventContent()+"','"+shiCs.getOrganizer()+"','"+shiCs.getPlace()+"','"+shiCs.getCost()+"','"+shiCs.getPhone()+"','"+shiCs.getEventCategory()+"','"+shiCs.getEmail()+"','http://booking.codeecstasy.com','images/event_2.jpg')";
		int num =BaseDao.executeUpdate(sql);
		System.out.println(sql);
		return num;
	}

	@Override
	public int InsertJuan(GreenUser greenUser) {
		// TODO Auto-generated method stub
		String sql = "INSERT INTO contribution(donationName,donationAddress,donationEmail,country,donationPhone,donationMoney,donationAmount) VALUES('"+greenUser.getDonationName()+"','"+greenUser.getDonationAddress()+"','"+greenUser.getDonationEmail()+"','"+greenUser.getCountry()+"','"+greenUser.getDonationPhone()+"','"+greenUser.getDonationMoney()+"','"+greenUser.getRemark()+"')";
		int num =BaseDao.executeUpdate(sql);
		System.out.println(sql);
		return num;
	}

	@Override
	public boolean UpdetJuan(GreenUser greenUser) {
		// TODO Auto-generated method stub
		String sql = "UPDATE contribution SET donationName=?,donationAddress=?,donationEmail=?,country=?,donationPhone=?,donationMoney =?,donationAmount=? WHERE id=?";
		System.out.println(sql);
		Object[] obj= {greenUser.getDonationName(),greenUser.getDonationAddress(),greenUser.getDonationEmail(),greenUser.getCountry(),greenUser.getDonationPhone(),greenUser.getDonationMoney(),greenUser.getRemark(),greenUser.getId()};
		int num=BaseDao.executeUpdate(sql,obj);
		if(num==1) {
			return true;
		}
		return false;
	}

	@Override
	public int updates(ShiC shic) {
		// TODO Auto-generated method stub
		String sql = "UPDATE newNventContent SET newNventContent=?,place=?,phone=?,eventCategory=? WHERE id=?";
		System.out.println(sql);
		Object[] objs= {shic.getCout(),shic.getPlace(),shic.getPhone(),shic.getEventCategory(),shic.getId()};
		int num=BaseDao.executeUpdate(sql,objs);
		return num;
	}

	@Override
	public int UpdetYuan(YuanC yuanC) {
		// TODO Auto-generated method stub
		String sql = "UPDATE donations SET title=?,author=?,theme=?,targets=?,collect=? WHERE id=?";
		System.out.println(sql);
		Object[] objs= {yuanC.getTitle(),yuanC.getAuthor(),yuanC.getTheme(),yuanC.getTargets(),yuanC.getCollect(),yuanC.getId()};
		int num=BaseDao.executeUpdate(sql,objs);
		return num;
	}

	@Override
	public int UpdetYuans(YuanC yuanC) {
		// TODO Auto-generated method stub
		String sql = "UPDATE donationsContent SET donationsContent=?,donationsContents=? WHERE id=?";
		System.out.println(sql);
		Object[] objs= {yuanC.getNei(),yuanC.getYuanNei(),yuanC.getId()};
		int num=BaseDao.executeUpdate(sql,objs);
		return num;
	}

	@Override
	public List<Tu> queryByIdAll(Tu tu) {
		// TODO Auto-generated method stub
List<Tu> userList = new ArrayList<Tu>();
		
		try {
			Connection conn = BaseDao.getConn();
			Statement ps = conn.createStatement();
			String sql = "SELECT * FROM gallery WHERE 1 = 1 ";
			//动态sql语句
			if (tu.getId() != 0) {
			    sql += " and id like '%" + tu.getId() + "%' ";
			}
			    if (tu.getImg() != null && !tu.getImg().isEmpty()) {
			        sql += " AND img LIKE '%" + tu.getImg() + "%' ";
			    }		    
			System.out.println(sql);
			ResultSet rs = ps.executeQuery(sql);
			while (rs.next()) {
				Tu rsUser = new Tu();
				rsUser.setId(rs.getInt("id"));
				rsUser.setImg(rs.getString("img"));
				userList.add(rsUser);
			}
			BaseDao.closeAll(conn,ps,rs);
		} catch (Exception e) {
			// TODO: handle exception
		}
		return userList;
	}

	@Override
	public List<ShiC> queryByShic(){
		// TODO Auto-generated method stub
		List<ShiC> userList = new ArrayList<ShiC>();
		try {
			Connection conn = BaseDao.getConn();
			Statement ps = conn.createStatement();
			String sql = "SELECT * FROM newNvent";
			//动态sql语句
			System.out.println(sql);
			ResultSet rs = ps.executeQuery(sql);
			while (rs.next()) {
				ShiC rsUser = new ShiC();
				rsUser.setId(rs.getInt("id"));
				rsUser.setAuthor(rs.getString("author"));
				rsUser.setDates(rs.getString("dates"));
				rsUser.setTitle(rs.getString("title"));
				userList.add(rsUser);
			}
			
			// 第二个查询语句
				String sql2 = "SELECT * FROM newNventContent";
				 System.out.println(sql2);
			        ps = conn.createStatement();
			        rs = ps.executeQuery(sql2);
	        int i = 0;
	        while (rs.next() && i < userList.size()) {
	        	ShiC rsUser = userList.get(i);
	            // 注意这里需要根据具体需求调整，假设 newNventContent 表的字段如下
	        	rsUser.setCout(rs.getString("newNventContent"));
				rsUser.setEventCategory(rs.getString("eventCategory"));
				rsUser.setPhone(rs.getString("phone"));
				rsUser.setPlace(rs.getString("place"));
	            i++;
	        }
	        BaseDao.closeAll(conn, ps, rs);
		}catch (SQLException e) {
		        e.printStackTrace();
		    } 
		return userList;
	}

	@Override
	public List<YuanC> queryByAllYuan() {
		// TODO Auto-generated method stub
List<YuanC> userList = new ArrayList<YuanC>();
		
		try {
			Connection conn = BaseDao.getConn();
			Statement ps = conn.createStatement();
			String sql = "SELECT * FROM donations";
			//动态sql语句
			System.out.println(sql);
			ResultSet rs = ps.executeQuery(sql);
			while (rs.next()) {
				YuanC rsUser = new YuanC();
				rsUser.setId(rs.getInt("id"));
				rsUser.setAuthor(rs.getString("author"));
				rsUser.setCollect(rs.getInt("collect"));
				rsUser.setTargets(rs.getInt("targets"));
				rsUser.setTheme(rs.getString("theme"));
				rsUser.setTitle(rs.getString("title"));
				userList.add(rsUser);
			}
			
			// 第二个查询语句
				String sql2 = "SELECT * FROM donationsContent";
				    System.out.println(sql2);
			        ps = conn.createStatement();
			        rs = ps.executeQuery(sql2);
	        int i = 0;
	        while (rs.next() && i < userList.size()) {
	        	YuanC rsUser = userList.get(i);
	            // 注意这里需要根据具体需求调整，假设 newNventContent 表的字段如下
	        	rsUser.setNei(rs.getString("donationsContent"));
				rsUser.setYuanNei(rs.getString("donationsContents"));
	            i++;
	        }
	        BaseDao.closeAll(conn, ps, rs);
		}catch (SQLException e) {
		        e.printStackTrace();
		    } 
		return userList;
		
	}

}
